Create Backup Set
From v7.11.0.0 onwards, %edition_name% supports 2 backup modes when creating a backup set for MS SQL Server, VSS mode and ODBC mode.
Backup Requirements
Please ensure that the following requirements are met:
- For VSS Backup Mode: The VSS-based backup utilizing the Microsoft SQL Server VSS Writer to obtain a consistent snapshot of the MS SQL databases, no spooling / staging of database file(s) is required during the backup process.
- Make sure the latest version of %edition_name% has been installed on the MS SQL server.
- Make sure the Microsoft SQL Server feature has been enabled as an add-on module in your %edition_name% user account.
- Make sure that your %edition_name% user account has sufficient storage quota assigned to accommodate the storage of MS SQL Server backup set and retention policy.
- The Continuous backup add-on module is required if you would like to enable the continuous backup feature.
- The default Java heap size setting on %edition_name% is 2048MB. For MS SQL Server backup it is highly recommended to increase the Java heap size setting to be at least 4096MB to improve backup and restore performance. The actual heap size is dependent on amount of free memory available on your MS SQL server.
- Make sure operating system account that performs the backup and restore has the sufficient permission to access both SQL server and VSS.
- The temporary directory folder is used by %edition_name% for storing backup set index files and incremental/differential delta files. To ensure optimal backup/restoration performance, it is recommended that the temporary directory folder is set to a local drive. The temporary folder should not be located on Windows system partition or the database partition to minimize any potential performance impact on Windows or database.
- It is recommended that the temporary directory folder should have at least free disk space of 50% of the total database size because the default Delta ratio is 50%. The actual free disk space required depends on various factors including the size of the database, number of backup destinations, backup frequency, in-file delta settings etc.
- The SQL Windows service must have read and write permission to the temporary directory.
- Make sure the SqlServerWriter is installed and running on the SQL server, and the writer state is Stable. This can be verified by running the vssadmin list writers command.
C:\Users\Administrator>vssadmin list writers
vssadmin 1.1 - Volume Shadow Copy Service administrative command-line tool
(C) Copyright 2001-2012 Microsoft Corp.
Writer name: 'Task Scheduler Writer'
Writer Id: {d61d61c8-d73a-4eee-8cdd-f6f9786b7124}
Writer Instance Id: {1bddd48e-5052-49db-9b07-b96f96727e6b}
State: [1] Stable
Last error: No error
Writer name: 'VSS Metadata Store Writer'
Writer Id: {75dfb225-e2e4-4d39-9ac9-ffaff65ddf06}
Writer Instance Id: {088e7a7d-09a8-4cc6-a609-ad90e75ddc93}
State: [1] Stable
Last error: No error
Writer name: 'Performance Counters Writer'
Writer Id: {0bada1de-01a9-4625-8278-69e735f39dd2}
Writer Instance Id: {f0086dda-9efc-47c5-8eb6-a944c3d09381}
State: [1] Stable
Last error: No error
Writer name: 'SqlServerWriter'
Writer Id: {a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}
Writer Instance Id: {3de4f842-4d57-4198-9949-3b3f8c2629dc}
State: [1] Stable
Last error: No error
Writer name: 'System Writer'
Writer Id: {e8132975-6f93-4464-a53e-1050253ae220}
Writer Instance Id: {635755cd-b461-426c-89d1-95682a185005}
State: [1] Stable
Last error: No error
Writer name: 'ASR Writer'
Writer Id: {be000cbe-11fe-4426-9c58-531aa6355fc4}
Writer Instance Id: {b01dbf5b-b437-48ca-882f-c7ec08ef0a50}
State: [1] Stable
Last error: No error
Writer name: 'WMI Writer'
Writer Id: {a6ad56c2-b509-4e6c-bb19-49d8f43532f0}
Writer Instance Id: {792c367d-1e21-4644-a63a-7d8ee4e2aaac}
State: [1] Stable
Last error: No error
Writer name: 'BITS Writer'
Writer Id: {4969d978-be47-48b0-b100-f328f07ac1e0}
Writer Instance Id: {7993a1e2-e51d-461f-b6f9-e64c93fad1ae}
State: [1] Stable
Last error: No error
Writer name: 'Registry Writer'
Writer Id: {afbab4a2-367d-4d15-a586-71dbb18f8485}
Writer Instance Id: {723e8efc-3758-40c4-aaeb-88a4f43702d3}
State: [1] Stable
Last error: No error
Writer name: 'Shadow Copy Optimization Writer'
Writer Id: {4dc3bdd4-ab48-4d07-adb0-3bee2926fd7f}
Writer Instance Id: {82fd1722-afcd-4f8e-95fd-8e5a2111c84c}
State: [1] Stable
Last error: No error
Writer name: 'COM+ REGDB Writer'
Writer Id: {542da469-d3e1-473c-9f4f-7847f01fc64f}
Writer Instance Id: {8fdde399-1d13-40e7-97c5-595bbf52b291}
State: [1] Stable
Last error: No error
- MS SQL Server volumes must use a file system which supports the use of VSS snapshot, for example NTFS.
- Make sure the following Windows services have been enabled.
- SQL Server VSS Writer
- SQL Server Services
- Volume Shadow Copy
- Make sure that the MS SQL entry is present in the registry key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL".
- VSS backup mode does not support backup of transaction log files, but for databases configured in either Full or Bulk-logging recovery model, this may eventually result in transaction logs filling up the available disk space on the volume of the MS SQL Server.
Refer to the following URL for more details: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/view-or-change-the-recovery-model-of-a-database-sql-server
https://technet.microsoft.com/en-us/library/cc966520.aspx
To prevent this from occurring, you can modify the recovery model of database selected for backup to Simple. Alternatively, to truncate the transaction log files, you can perform a transaction log backup manually, or create an additional MS SQL database backup set in ODBC backup mode to perform a transaction log backup. Please refer to ODBC backup mode for further details.
- For ODBC Backup Mode: By using the ODBC mode for MS SQL backup, databases files are spooled to a temporary directory before being uploaded to the backup destination.
- Make sure the latest version of %edition_name% has been installed on the MS SQL server.
- Make sure the Microsoft SQL Server feature has been enabled as an add-on module in your %edition_name% user account.
- Make sure that your %edition_name% user account has sufficient storage quota assigned to accommodate the storage of MS SQL Server backup set and retention policy.
- The Continuous backup add-on module is required if would like to enable the continuous backup feature.
- The default Java heap size setting on %edition_name% is 2048MB. For MS SQL Server backup it is highly recommended to increase the Java heap size setting to be at least 4096MB to improve backup and restore performance. The actual heap size is dependent on amount of free memory available on your MS SQL server.
- The temporary directory folder is used by %edition_name% for storing the database files, incremental/differential delta files and backup set index files. To ensure optimal backup/restoration performance, it is recommended that the temporary directory folder is set to a local drive.
The temporary folder should not be located on Windows system partition or the database partition to minimize any potential performance impact on Windows or database. If the temporary directory folder is located on a network drive, make sure the login account has sufficient permission to access the network resources.
Please refer to the following URL for more details: https://support.microsoft.com/en-us/help/2926557/sql-server-vdi-backup-and-restore-operations-require-sysadmin-privileg
https://technet.microsoft.com/en-us/library/cc966520.aspx
- It is recommended that the temporary directory folder should have at least free disk space of 150% of the total database size. The actual free disk space required depends on various factors including the size of the database, number of backup destinations, backup frequency, in-file delta settings etc.
- The SQL Windows service must have read and write permission to the temporary directory.
- Make sure the following Windows services have been enabled.
- SQL Server Services
- Volume Shadow Copy
- Make sure that the MS SQL entry is present in the registry key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL".
- Maximum Worker Thread: For SQL instance with large number of database (more than 500 databases), consider to increase the "Maximum Worker Thread" setting. Refer to the article below for further details.
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-worker-threads-server-configuration-option
- ODBC backup mode supports transaction log backup for database with Full recovery model.
Considerations for backup and restore of system databases
Refer to the following tables for considerations for backup and restore of system databases:
Considerations for backup of system databases:
SQL server maintains a set of system level database which are essential for the operation of the server instance.
Several of the system databases must be backed up after every significant update, they includes:
- master
- model
- msdb
- distribution (for SQL database with replication enabled)
This table summarizes all of the system databases.
System database | Description | Backup required | Suggestion |
master | The database that records all of the system level information of a SQL server system. | Yes | To back up any database, the instance of SQL server must be running. Startup of an instance of SQL server requires the master database is accessible and at least party usable. Back up the master database as often as necessary to protect the data sufficiently for your business needs. Microsoft recommends a regular backup schedule, which you can supplement with manual backup after any substantial update. |
model | The template for all databases that are created on the instance of SQL server. | Yes | Backup the model database only when necessary, for example, after customizing its database options.
Microsoft recommends that you create only full database backups of model, as required. Because model is small and rarely changes, backing up the log is unnecessary. |
msdb | The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and for recording operators.
It also contains history tables (e.g. backup / restore history table). | Yes | Back up msdb whenever it is updated. |
tempdb | A workspace for holding temporary or intermediate result sets.
This database is recreated every time an instance of SQL server is started. | No | The tempdb system database cannot be backed up. |
distribution | The distribution database exists only if the server is configured as a replication distributor.
It stores metadata and history data for all types of replication, and transactions for transactional replication. | Yes | Replicated databases and their associated system databases should be backed up regularly. |
Considerations for restore of system databases:
System database | Restore suggestion |
master | To restore any database, the instance of SQL server must be running. Startup of an instance of SQL server requires that the master database is accessible and at least party useable.
Restore or rebuild the master database completely if master becomes unusable. |
model | Restore the model database if:
- The master database has been rebuilt.
- The model database has been damaged, for example due to media failure.
- The model database has been modified, in this case, it is necessary to restore model from a backup when you rebuild master, because the Rebuild Master utility deletes and recreates model.
|
msdb | Restore the msdb database if the master database has been rebuilt. |
distribution | For restore strategies of distribution database, please refer to the following online document from Microsoft for more details:
http://msdn.microsoft.com/en-us/library/ms152560.aspx
|
Limitation
- %edition_name% does not support backup of MS SQL server in cluster environment, only standalone environment is supported.
- For VSS backup mode:
- Only support backup of database on local drive. Database on network drive is not supported.
- VSS backup mode does not support transaction log backup. Therefore, transaction log backup must be performed manually. Alternatively, create an additional MS SQL database backup set in ODBC backup mode for transaction log backup.
- In order to truncate the transaction logs, you have to perform a manual log truncation, which could be time consuming.
- You cannot create database snapshots on FAT32 file system or RAW partitions. The sparse files used by database snapshots are provided by the NTFS file system.
- SQL Server Version:
- If you have chosen the automated restoration to the Original SQL server or Alternate SQL server of your selection, the restoration can only be done in a SQL server version that is the same as the one used for performing the backup.
- If you have chosen to restore the raw file, the raw database file(s) can be manually restored to the same or newer SQL server version that you used to perform the backup.
- Restore to other SQL server:
- If you would like to restore database to an alternate SQL server, you can only choose to restore one database at a time.
- If you would like to restore database to an alternate SQL server, make sure you choose to restore raw file by enabling the checkbox Restore raw file.
Best Practice and Recommendation
- For VSS backup mode, it is suggested to set the backup schedule to a time when system activity is low to achieve the best possible performance.
- It is recommended to use ODBC backup mode for backup of database with a high volume of transaction, since such setup may require frequent backups. Transaction log backup (which is only supported by ODBC backup mode) can be performed periodically, and is less resource intensive than VSS based backup.
- For maximum data protection and restore options, it is recommended to configure:
At least one offsite or cloud destination
At least one local destination for fast recovery
- Perform test restores periodically to ensure your backup is set up and performed properly. Performing recovery test can also help identify potential issues or gaps in your recovery plan. It's important that you do not try to make the test easier, as the objective of a successful test is not to demonstrate that everything is flawless. There might be flaws identified in the plan throughout the test and it is important to identify those flaws.
- The Restore Raw File option is for advanced MS SQL Server administrator and should only be used if you have in-depth knowledge and understanding of your MS SQL Server, otherwise, it is not recommended to use this option as there are additional MS SQL techniques required to perform the manual restore.
Set the name of the backup set
Key:
Field |
Description |
Name |
This is the name of the backup set. You can create a meaningful name for it.
|
Backup set type |
Enter the correct backup type from the drop down box.
|
Backup mode |
Select the backup mode from the drop down box.
|
Server |
Select to backup multiple SQL instances or a specific instance from the drop down box.
|
Login ID |
The login ID to access your MS SQL server. Default is sa.
|
Password |
The login password to your MS SQL server.
|
To create a backup set name:
- Type in a meaningful backup set name.
- Enter correct backup set type, eg: MS SQL Server Backup.
- Select the backup mode from the drop down box, eg: VSS (without staging data; support full, differential and incremental backup), ODBC (with staging data; support full, differential and transaction log backup).
- Select to back up multiple SQL instances or a specific instance from the drop down list.
- Change the Login ID if necessary, by default 'sa'.
- Enter correct password.
- Click [Next] button to continue.
Note: Click [Next] button will start the verifying process.